﻿Imports Oracle.DataAccess.Client
Public Class C_SQL
#Region "初始化"


    Implements IDisposable

    Dim cn As OracleConnection
    Public Sub New(DBPath As String)
        cn = New OracleConnection(DBPath)
    End Sub
#Region "IDisposable Support"
    Private disposedValue As Boolean ' 要检测冗余调用

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not disposedValue Then
            If disposing Then
                ' TODO: 释放托管状态(托管对象)。
                Try
                    cn.Dispose()
                Catch ex As Exception

                End Try
            End If

            ' TODO: 释放未托管资源(未托管对象)并在以下内容中替代 Finalize()。
            ' TODO: 将大型字段设置为 null。
        End If
        disposedValue = True
    End Sub

    ' TODO: 仅当以上 Dispose(disposing As Boolean)拥有用于释放未托管资源的代码时才替代 Finalize()。
    'Protected Overrides Sub Finalize()
    '    ' 请勿更改此代码。将清理代码放入以上 Dispose(disposing As Boolean)中。
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' Visual Basic 添加此代码以正确实现可释放模式。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' 请勿更改此代码。将清理代码放入以上 Dispose(disposing As Boolean)中。
        Dispose(True)
        ' TODO: 如果在以上内容中替代了 Finalize()，则取消注释以下行。
        ' GC.SuppressFinalize(Me)
    End Sub
#End Region
#End Region
#Region "GetDataTable "
    Function GetDataTable(ByVal SuperSQL As String, Optional TimeOut As Integer = 300) As System.Data.DataTable
        Try
            Using UPData_command As New OracleCommand
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandText = SuperSQL
                Dim DT_This = New DataTable
                Using rdr As OracleDataReader = UPData_command.ExecuteReader()
                    DT_This.Load(rdr)
                End Using
                Return DT_This
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
    Function GetDataSet(ByVal SuperSQL As String, Optional TimeOut As Integer = 300) As System.Data.DataSet
        Try
            Using UPData_command As New OracleCommand
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandText = SuperSQL
                Dim DS = New DataSet
                Using rdr As OracleDataReader = UPData_command.ExecuteReader()
                    Dim str() As String = {}
                    DS.Load(rdr, LoadOption.Upsert, str)
                End Using
                Return DS
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "DataTable"
    Public Sub Fresh_Table(ByVal Table_Name As String, ByRef DT_This As Data.DataTable,
                                Optional ByVal Filterstring As String = Nothing,
                Optional ByVal TopNum As Integer = -1,
                Optional ByVal ColumnsNames() As String = Nothing,
                Optional ByVal OrderOrGroupstring As String = "",
                Optional TimeOut As Integer = 300) '刷新数据库到数据表
        Try
            Dim SQLStr As String = DCM.SQLStr_Select(Table_Name, Filterstring, TopNum, ColumnsNames, OrderOrGroupstring, DCM.Enum_DataBaseType.Oracle)
            Fresh_Table(DT_This, SQLStr, TimeOut)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub Fresh_Table(ByRef DT_This As Data.DataTable,
                           SQLStr As String,
                           Optional TimeOut As Integer = 300) '刷新数据库到数据表
        Try
            If cn.State = ConnectionState.Closed Then cn.Open()
            Using comm As New OracleCommand(SQLStr, cn)
                comm.CommandTimeout = TimeOut
                If IsNothing(DT_This) Then DT_This = New DataTable
                'Using dataAdapter As New Oracle.ManagedDataAccess.Client.OracleDataAdapter()
                'dataAdapter.Fill(DT_This)
                'End Using
                Using rdr As OracleDataReader = comm.ExecuteReader()
                    DT_This.Load(rdr)
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Sub


    Public Function GetDataTableBySQL(SQLStr As String,
                          Optional TimeOut As Integer = 300) As DataTable  '刷新数据库到数据表
        Try
            If cn.State = ConnectionState.Closed Then cn.Open()
            Using comm As New OracleCommand(SQLStr, cn)
                comm.CommandTimeout = TimeOut
                Dim DT_This As New DataTable
                Using rdr As OracleDataReader = comm.ExecuteReader()
                    DT_This.Load(rdr)
                End Using
                Return DT_This
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region
#Region "批量导入数据库"

    Function BctchAdd_To_DateBase_old(DT_This As DataTable, TBName_This As String, Optional TimeOut As Integer = 300)
        Try
            Dim DT_Aim As New DataTable
            Fresh_Table(TBName_This, DT_Aim,    , 0)
            Dim DT_Add = DCM.SQL_StrDll.批量导入验证并制作批量导入表格(DT_This, DT_Aim)
            '开始导入
            Dim MyBulk As New Oracle.DataAccess.Client.OracleBulkCopy(cn)
            MyBulk.BulkCopyTimeout = TimeOut
            MyBulk.DestinationTableName = TBName_This
            If cn.State = ConnectionState.Closed Then cn.Open()
            MyBulk.WriteToServer(DT_Add)
            cn.Close()
            Return DT_Add.Rows.Count
        Catch ex As Exception
            Throw New Exception("批量导入数据发生错误:" & ex.Message)
        End Try
    End Function


#End Region
#Region "返回第一行第一列数据"
    Function GetObject(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Object
        Try
            Using sqlCommand As OracleCommand = New OracleCommand(SuperSQL, cn)
                sqlCommand.CommandTimeout = TimeOut
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return sqlCommand.ExecuteScalar
            End Using
        Catch ex As Exception
            Throw New NotSupportedException(ex.Message)
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "直接返回影响值"
    Function effect(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Integer
        Try
            Using sqlCommand As OracleCommand = New OracleCommand(SuperSQL, cn)
                sqlCommand.CommandTimeout = TimeOut
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return sqlCommand.ExecuteNonQuery
            End Using
        Catch ex As Exception
            Throw New NotSupportedException(ex.Message)
        Finally
            cn.Close()
        End Try
    End Function
#End Region
#Region "插入数据库"
    Function Insert(ByVal SuperSQL As String, Optional TimeOut As Integer = 30) As Object
        Try
            Using UPData_command As New OracleCommand
                Dim BackID As String = ""
                UPData_command.CommandTimeout = TimeOut
                UPData_command.Connection = cn
                UPData_command.CommandType = CommandType.Text
                UPData_command.CommandText = SuperSQL & " select @@IDENTITY"
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return UPData_command.ExecuteScalar()
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function
    Public Function Insert(ByVal TBName_THis As String, ByVal Columns() As String, ByVal Values() As Object, TimeOut As Integer) As Object
        Return Insert_Tran(TBName_THis, Columns, Values, TimeOut, Nothing, True)
    End Function
    Public Function Insert_Tran(ByVal TBName_THis As String, ByVal Columns As List(Of String), ByVal Values As List(Of Object), TimeOut As Integer, Optional tranction As OracleTransaction = Nothing, Optional NeedIDENTITY As Boolean = True) As Object
        Return Insert_Tran(TBName_THis, Columns.ToArray, Values.ToArray, TimeOut, tranction, NeedIDENTITY)
    End Function
    Public Function Insert_Tran(ByVal TBName_THis As String, ByVal Columns() As String, ByVal Values() As Object, Optional TimeOut As Integer = 30, Optional tranction As OracleTransaction = Nothing, Optional NeedIDENTITY As Boolean = True) As Object
        Dim SqlStr As String = "未成功创建SQl语句"
        Try
            Using UPData_command As New OracleCommand
                UPData_command.Transaction = tranction
                UPData_command.Connection = cn
                UPData_command.CommandTimeout = TimeOut
                UPData_command.CommandType = CommandType.Text
                Dim Columnstring As String = ""
                Dim Valuestring As String = ""
                For i As Integer = 0 To Columns.Count - 1
                    Columnstring += "[" & Columns(i) & "] ,"
                    Valuestring += "@a" & i & " ,"
                    Select Case True
                        Case Values(i).GetType Is GetType(String)
                            UPData_command.Parameters.Add("@a" & i, System.Data.DbType.String).Value = Values(i)
                        Case Values(i).GetType Is GetType(Date) Or Values(i).GetType Is GetType(DateTime)
                            UPData_command.Parameters.Add("@a" & i, System.Data.DbType.DateTime).Value = Values(i)
                        Case Else
                            UPData_command.Parameters.Add("@a" & i, System.Data.DbType.String).Value = DCM.CaseDBNullToString(Values(i))
                    End Select
                Next
                Columnstring = DCM.StringDLL_切掉字符串_后(Columnstring, ",")
                Valuestring = DCM.StringDLL_切掉字符串_后(Valuestring, ",")
                SqlStr = "insert into " & TBName_THis & "(" & Columnstring & ") values (" & Valuestring & ")" & If(NeedIDENTITY, "select @@IDENTITY", "")
                UPData_command.CommandText = SqlStr
                If cn.State = ConnectionState.Closed Then cn.Open()
                Return UPData_command.ExecuteScalar
            End Using
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function

#End Region
#Region "批量导入数据库"
    Function BctchAdd_To_DateBase(合并提交SQLstr As String, DT_This As DataTable, TBName_This As String, Optional TimeOut As Integer = 300) As Integer
        If 合并提交SQLstr = "" Then
            Return BctchAdd_To_DateBase_new(DT_This, TBName_This, TimeOut)
        Else


            Using sct As New SQLServer_CMD_Tran({New SQLServer_CMD_Tran_item("", 合并提交SQLstr),
                                                 New SQLServer_CMD_Tran_item(DT_This, TBName_This)}, Me)
                sct.ExecuteNonQuery()
                If sct.Success = True Then
                    sct.Commit()
                    Return sct.EffectCount
                Else
                    Throw New Exception(sct.ErrStr)
                End If
            End Using
        End If
    End Function
    Function BctchAdd_To_DateBase_new(DT_This As DataTable,
                                      TBName_This As String,
                                      Optional TimeOut As Integer = 300,
                                      Optional tranction As OracleTransaction = Nothing) As Integer
        Try
            Dim DT_Aim = GetDataTable(DCM.SQLStr_Select(TBName_This, "", 0,,, DCM.SQL_StrDll.Enum_DataBaseType.SQlite), TimeOut)
            Dim DT_Add = DCM.SQL_StrDll.批量导入验证并制作批量导入表格(DT_This, DT_Aim)
            '开始导入
            Dim Colnames As New List(Of String)
            For i As Integer = 0 To DT_Add.Columns.Count - 1
                Colnames.Add(DT_Add.Columns(i).ColumnName)
            Next
            If IsNothing(tranction) Then
                Using tranction1 As OracleTransaction = cn.BeginTransaction()
                    For Each dr As DataRow In DT_Add.Rows
                        Dim Values As New List(Of Object)
                        For i As Integer = 0 To DT_Add.Columns.Count - 1
                            Values.Add(dr(i))
                        Next
                        Insert_Tran(TBName_This, Colnames, Values, TimeOut, tranction1, False)
                    Next
                    tranction1.Commit()
                End Using
            Else
                For Each dr As DataRow In DT_Add.Rows
                    Dim Values As New List(Of Object)
                    For i As Integer = 0 To DT_Add.Columns.Count - 1
                        Values.Add(dr(i))
                    Next
                    Insert_Tran(TBName_This, Colnames, Values, TimeOut, tranction, False)
                Next
            End If
            Return DT_Add.Rows.Count
        Catch ex As Exception
            Throw New Exception("批量导入数据发生错误:" & ex.Message)
        End Try
    End Function
#Region "事务_新"
    Public Class SQLServer_CMD_Tran
        Implements IDisposable
        Public CSQL As C_SQL
        Dim CMD As OracleCommand
        Public Success As Boolean
        Public ErrStr As String
        Public Items() As SQLServer_CMD_Tran_item
        Dim ISExecuteNonQuery As Boolean = False
        Public Function EffectCount() As Integer
            If IsNothing(Items) Then Throw New Exception("未正常初始化执行语句列表!")
            Dim rcount As Integer = 0
            For Each item In Items
                rcount += item.EffectCount
            Next
            Return rcount
        End Function
        Public Sub ExecuteNonQuery()
            Try
                If ISExecuteNonQuery Then Return
                ISExecuteNonQuery = True
                If IsNothing(CMD) = False Then CMD.Dispose()
                CMD = New OracleCommand("", CSQL.cn)
                If IsNothing(CSQL.cn) Then Throw New Exception("未初始化数据库连接")
                If CSQL.cn.State = ConnectionState.Closed Then CSQL.cn.Open()
                CMD.Transaction = CSQL.cn.BeginTransaction
                If IsNothing(Items) Then Throw New Exception("未正常初始化执行语句列表!")
                For i As Integer = 0 To Items.Count - 1
                    Select Case Items(i).方式
                        Case SQLServer_CMD_Tran_item.Enum_方式.DataTable
                            Items(i).EffectCount = CSQL.BctchAdd_To_DateBase_new(Items(i).data, Items(i).TBName, 300, CMD.Transaction)
                        Case SQLServer_CMD_Tran_item.Enum_方式.Text
                            CMD.CommandText = Items(i).SQLStr
                            Try
                                Items(i).EffectCount = CMD.ExecuteNonQuery()
                            Catch ex As Exception
                                Throw New Exception(Items(i).TBName & "执行失败:" & ex.Message)
                            End Try
                    End Select
                Next
                Success = True
            Catch ex As Exception
                ErrStr = ex.Message
                Success = False
            End Try
        End Sub
        Public Sub Commit()
            If IsNothing(CMD) Then Throw New Exception("未执行sql语句")
            CMD.Transaction.Commit()
            ISExecuteNonQuery = False
        End Sub
        Public Sub RollBack()
            If IsNothing(CMD) Then Throw New Exception("未执行sql语句")
            CMD.Transaction.Rollback()
            ISExecuteNonQuery = False
        End Sub
        Public Sub New(Items() As SQLServer_CMD_Tran_item, CQSL As C_SQL)
            Me.Items = Items
            Me.CSQL = CSQL
        End Sub

#Region "IDisposable Support"
        Private disposedValue As Boolean ' 检测冗余的调用

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO:  释放托管状态(托管对象)。
                    If IsNothing(CMD) = False Then
                        If IsNothing(CMD.Transaction) = False Then CMD.Transaction.Dispose()
                        CMD.Dispose()
                    End If
                End If

                ' TODO:  释放非托管资源(非托管对象)并重写下面的 Finalize()。
                ' TODO:  将大型字段设置为 null。
            End If
            Me.disposedValue = True
        End Sub

        ' TODO:  仅当上面的 Dispose(ByVal disposing As Boolean)具有释放非托管资源的代码时重写 Finalize()。
        'Protected Overrides Sub Finalize()
        '    ' 不要更改此代码。    请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' Visual Basic 添加此代码是为了正确实现可处置模式。
        Public Sub Dispose() Implements IDisposable.Dispose
            ' 不要更改此代码。    请将清理代码放入上面的 Dispose (disposing As Boolean)中。
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class
    Public Class SQLServer_CMD_Tran_item
        Public TBName As String = ""
        Public SQLStr As String = ""
        Public EffectCount As Integer = 0
        Public data As DataTable = Nothing
        Public Enum Enum_方式 As Integer
            Text = 0
            DataTable = 1
        End Enum
        Public 方式 As Enum_方式 = Enum_方式.Text
        Public Sub New(dt As DataTable, TBName As String)
            data = dt
            方式 = Enum_方式.DataTable
            Me.TBName = TBName
        End Sub
        Public Sub New(TBName As String, SQLStr As String)
            Me.TBName = TBName
            Me.SQLStr = SQLStr
        End Sub
        Public Sub New()
        End Sub
    End Class
#End Region
#End Region
End Class